using System.Collections.Concurrent;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using JGSY.CMS.LowCode.Platform.Infrastructure.CmsDbContext;
using JGSY.CMS.LowCode.Platform.Infrastructure.Services.Performance;

namespace JGSY.CMS.LowCode.Platform.Infrastructure.Services.Performance
{
    /// <summary>
    /// 数据库查询优化服务接口
    /// 提供智能查询优化、慢查询检测和数据库性能分析
    /// </summary>
    public interface IQueryOptimizationService
    {
        Task<QueryResult<T>> ExecuteOptimizedQueryAsync<T>(IQueryable<T> query, string operationName, string? tenantId = null) 
            where T : class;
        Task<List<SlowQuery>> GetSlowQueriesAsync(string? tenantId = null, TimeSpan? timeRange = null);
        Task<QueryPerformanceReport> GetQueryPerformanceReportAsync(string? tenantId = null);
        Task OptimizeQueryAsync(string querySignature);
        Task WarmupQueriesAsync(string tenantId);
    }

    /// <summary>
    /// 数据库查询优化服务实现
    /// 自动检测慢查询、提供查询优化建议、实现智能缓存
    /// </summary>
    public class QueryOptimizationService : IQueryOptimizationService
    {
        private readonly LowCodeDbContext _dbContext;
        private readonly ITenantCacheService _cacheService;
        private readonly ITenantPerformanceService _performanceService;
        private readonly ILogger<QueryOptimizationService> _logger;
        
        // 查询性能数据
        private readonly ConcurrentDictionary<string, QueryStats> _queryStats;
        private readonly ConcurrentDictionary<string, QueryPlan> _queryPlans;
        
        // 配置参数
        private readonly TimeSpan _slowQueryThreshold = TimeSpan.FromMilliseconds(1000);
        private readonly TimeSpan _cacheExpiry = TimeSpan.FromMinutes(15);

        public QueryOptimizationService(
            LowCodeDbContext dbContext,
            ITenantCacheService cacheService,
            ITenantPerformanceService performanceService,
            ILogger<QueryOptimizationService> logger)
        {
            _dbContext = dbContext;
            _cacheService = cacheService;
            _performanceService = performanceService;
            _logger = logger;
            _queryStats = new ConcurrentDictionary<string, QueryStats>();
            _queryPlans = new ConcurrentDictionary<string, QueryPlan>();
        }

        /// <summary>
        /// 执行优化查询
        /// 包含缓存检查、性能监控和自动优化
        /// </summary>
        public async Task<QueryResult<T>> ExecuteOptimizedQueryAsync<T>(IQueryable<T> query, string operationName, string? tenantId = null)
            where T : class
        {
            var querySignature = GenerateQuerySignature(query, operationName);
            var cacheKey = $"query:{querySignature}";
            
            using var performanceTracker = _performanceService.StartOperation(tenantId ?? "system", operationName);
            var stopwatch = System.Diagnostics.Stopwatch.StartNew();

            try
            {
                // 1. 尝试从缓存获取结果
                var cachedResult = await _cacheService.GetAsync<List<T>>(cacheKey, tenantId);
                if (cachedResult != null)
                {
                    stopwatch.Stop();
                    await _performanceService.RecordCacheOperationAsync(tenantId ?? "system", operationName, true, stopwatch.Elapsed);
                    
                    _logger.LogDebug("查询缓存命中: {Operation} ({Duration}ms)", operationName, stopwatch.Elapsed.TotalMilliseconds);
                    
                    return new QueryResult<T>
                    {
                        Data = cachedResult,
                        FromCache = true,
                        Duration = stopwatch.Elapsed,
                        QuerySignature = querySignature
                    };
                }

                // 2. 应用查询优化
                var optimizedQuery = await ApplyQueryOptimizationsAsync(query, querySignature);

                // 3. 执行查询
                var results = await optimizedQuery.ToListAsync();
                stopwatch.Stop();

                // 4. 记录性能数据
                await RecordQueryPerformanceAsync(querySignature, operationName, stopwatch.Elapsed, results.Count, tenantId);
                
                // 5. 缓存结果（如果适合缓存）
                if (ShouldCacheQuery(querySignature, stopwatch.Elapsed, results.Count))
                {
                    await _cacheService.SetAsync(cacheKey, results, _cacheExpiry, tenantId);
                    _logger.LogDebug("查询结果已缓存: {Operation}", operationName);
                }

                // 6. 检查是否为慢查询
                if (stopwatch.Elapsed > _slowQueryThreshold)
                {
                    await HandleSlowQueryAsync(querySignature, operationName, stopwatch.Elapsed, tenantId);
                }

                return new QueryResult<T>
                {
                    Data = results,
                    FromCache = false,
                    Duration = stopwatch.Elapsed,
                    QuerySignature = querySignature
                };
            }
            catch (Exception ex)
            {
                stopwatch.Stop();
                _logger.LogError(ex, "查询执行失败: {Operation} ({Duration}ms)", operationName, stopwatch.Elapsed.TotalMilliseconds);
                
                await _performanceService.RecordRequestAsync(tenantId ?? "system", operationName, stopwatch.Elapsed, false);
                throw;
            }
        }

        /// <summary>
        /// 获取慢查询列表
        /// </summary>
        public async Task<List<SlowQuery>> GetSlowQueriesAsync(string? tenantId = null, TimeSpan? timeRange = null)
        {
            var range = timeRange ?? TimeSpan.FromHours(24);
            var cutoffTime = DateTime.UtcNow - range;
            
            var slowQueries = _queryStats.Values
                .Where(s => (string.IsNullOrEmpty(tenantId) || s.TenantId == tenantId) &&
                           s.AverageDuration > _slowQueryThreshold &&
                           s.LastExecution >= cutoffTime)
                .OrderByDescending(s => s.AverageDuration)
                .Select(s => new SlowQuery
                {
                    QuerySignature = s.QuerySignature,
                    OperationName = s.OperationName,
                    TenantId = s.TenantId,
                    ExecutionCount = s.ExecutionCount,
                    AverageDuration = s.AverageDuration,
                    MaxDuration = s.MaxDuration,
                    LastExecution = s.LastExecution,
                    Recommendations = GenerateQueryRecommendations(s)
                })
                .ToList();

            await Task.CompletedTask;
            return slowQueries;
        }

        /// <summary>
        /// 获取查询性能报告
        /// </summary>
        public async Task<QueryPerformanceReport> GetQueryPerformanceReportAsync(string? tenantId = null)
        {
            var stats = _queryStats.Values
                .Where(s => string.IsNullOrEmpty(tenantId) || s.TenantId == tenantId)
                .ToList();

            var report = new QueryPerformanceReport
            {
                TenantId = tenantId,
                TotalQueries = stats.Sum(s => s.ExecutionCount),
                UniqueQueries = stats.Count,
                AverageQueryTime = stats.Any() ? TimeSpan.FromMilliseconds(stats.Average(s => s.AverageDuration.TotalMilliseconds)) : TimeSpan.Zero,
                SlowQueriesCount = stats.Count(s => s.AverageDuration > _slowQueryThreshold),
                CachedQueriesCount = stats.Count(s => s.CacheHitRate > 0),
                TopSlowQueries = stats
                    .Where(s => s.AverageDuration > _slowQueryThreshold)
                    .OrderByDescending(s => s.AverageDuration)
                    .Take(10)
                    .ToList(),
                QueryDistribution = GenerateQueryDistribution(stats),
                RecommendedOptimizations = GenerateOptimizationRecommendations(stats)
            };

            await Task.CompletedTask;
            return report;
        }

        /// <summary>
        /// 优化指定查询
        /// </summary>
        public async Task OptimizeQueryAsync(string querySignature)
        {
            if (_queryStats.TryGetValue(querySignature, out var stats))
            {
                var optimizationPlan = await GenerateOptimizationPlanAsync(stats);
                _queryPlans[querySignature] = new QueryPlan
                {
                    QuerySignature = querySignature,
                    OptimizationPlan = optimizationPlan,
                    CreatedAt = DateTime.UtcNow
                };

                _logger.LogInformation("查询优化计划已生成: {QuerySignature}", querySignature);
            }
        }

        /// <summary>
        /// 预热查询缓存
        /// </summary>
        public async Task WarmupQueriesAsync(string tenantId)
        {
            try
            {
                _logger.LogInformation("开始预热租户查询缓存: {TenantId}", tenantId);

                var warmupQueries = new List<Func<Task>>
                {
                    () => WarmupUserQueriesAsync(tenantId),
                    () => WarmupContentQueriesAsync(tenantId),
                    () => WarmupPermissionQueriesAsync(tenantId),
                    () => WarmupConfigQueriesAsync(tenantId)
                };

                // 并行执行预热查询
                await Task.WhenAll(warmupQueries.Select(query => query()));
                
                _logger.LogInformation("租户查询缓存预热完成: {TenantId}", tenantId);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "租户查询缓存预热失败: {TenantId}", tenantId);
            }
        }

        #region 私有方法

        /// <summary>
        /// 生成查询签名
        /// </summary>
        private string GenerateQuerySignature<T>(IQueryable<T> query, string operationName)
        {
            // 简化的查询签名生成 - 实际实现可能需要更复杂的逻辑
            var queryString = query?.ToString() ?? string.Empty;
            var hash = queryString.GetHashCode();
            return $"{operationName}_{typeof(T).Name}_{hash:X8}";
        }

        /// <summary>
        /// 应用查询优化
        /// </summary>
        private async Task<IQueryable<T>> ApplyQueryOptimizationsAsync<T>(IQueryable<T> query, string querySignature)
            where T : class
        {
            var optimizedQuery = query;

            // 检查是否有优化计划
            if (_queryPlans.TryGetValue(querySignature, out var plan))
            {
                // 应用优化计划
                optimizedQuery = ApplyOptimizationPlan(query, plan);
            }

            // 应用通用优化
            optimizedQuery = optimizedQuery.AsNoTracking(); // 禁用变更跟踪以提高性能

            await Task.CompletedTask;
            return optimizedQuery;
        }

        /// <summary>
        /// 应用优化计划
        /// </summary>
        private IQueryable<T> ApplyOptimizationPlan<T>(IQueryable<T> query, QueryPlan plan)
        {
            // 根据优化计划调整查询
            // 这里可以添加具体的优化逻辑，如：
            // - 添加适当的Include()
            // - 优化Where条件
            // - 添加合适的OrderBy()
            return query;
        }

        /// <summary>
        /// 记录查询性能
        /// </summary>
        private async Task RecordQueryPerformanceAsync(string querySignature, string operationName, TimeSpan duration, int resultCount, string? tenantId)
        {
            // 更新查询统计
            var stats = _queryStats.AddOrUpdate(querySignature, 
                new QueryStats
                {
                    QuerySignature = querySignature,
                    OperationName = operationName,
                    TenantId = tenantId ?? "system",
                    ExecutionCount = 1,
                    AverageDuration = duration,
                    MaxDuration = duration,
                    MinDuration = duration,
                    LastExecution = DateTime.UtcNow,
                    TotalResultCount = resultCount
                },
                (key, existingStats) =>
                {
                    existingStats.ExecutionCount++;
                    existingStats.LastExecution = DateTime.UtcNow;
                    existingStats.TotalResultCount += resultCount;
                    
                    // 更新平均执行时间
                    var totalMs = (existingStats.AverageDuration.TotalMilliseconds * (existingStats.ExecutionCount - 1)) + duration.TotalMilliseconds;
                    existingStats.AverageDuration = TimeSpan.FromMilliseconds(totalMs / existingStats.ExecutionCount);
                    
                    // 更新最大和最小执行时间
                    if (duration > existingStats.MaxDuration)
                        existingStats.MaxDuration = duration;
                    if (duration < existingStats.MinDuration)
                        existingStats.MinDuration = duration;
                    
                    return existingStats;
                });

            // 记录到性能服务
            await _performanceService.RecordQueryAsync(tenantId ?? "system", operationName, duration, resultCount);
        }

        /// <summary>
        /// 判断是否应该缓存查询
        /// </summary>
        private bool ShouldCacheQuery(string querySignature, TimeSpan duration, int resultCount)
        {
            // 缓存策略：
            // 1. 执行时间超过阈值
            // 2. 结果集不会太大
            // 3. 不是一次性查询
            return duration > TimeSpan.FromMilliseconds(100) && 
                   resultCount < 10000 &&
                   _queryStats.ContainsKey(querySignature);
        }

        /// <summary>
        /// 处理慢查询
        /// </summary>
        private async Task HandleSlowQueryAsync(string querySignature, string operationName, TimeSpan duration, string? tenantId)
        {
            _logger.LogWarning("检测到慢查询: {Operation} ({Duration}ms) - 租户: {TenantId}", 
                operationName, duration.TotalMilliseconds, tenantId);

            // 自动生成优化建议
            await OptimizeQueryAsync(querySignature);
        }

        /// <summary>
        /// 生成查询建议
        /// </summary>
        private List<string> GenerateQueryRecommendations(QueryStats stats)
        {
            var recommendations = new List<string>();

            if (stats.AverageDuration > TimeSpan.FromSeconds(2))
            {
                recommendations.Add("查询执行时间过长，建议添加适当的数据库索引");
            }

            if (stats.ExecutionCount > 1000 && stats.CacheHitRate < 0.5)
            {
                recommendations.Add("查询频率高但缓存命中率低，建议优化缓存策略");
            }

            if (stats.TotalResultCount / stats.ExecutionCount > 1000)
            {
                recommendations.Add("查询返回大量数据，建议添加分页或限制结果集大小");
            }

            return recommendations;
        }

        /// <summary>
        /// 生成优化计划
        /// </summary>
        private async Task<string> GenerateOptimizationPlanAsync(QueryStats stats)
        {
            var plan = new List<string>();

            if (stats.AverageDuration > TimeSpan.FromSeconds(1))
            {
                plan.Add("添加数据库索引以提高查询性能");
            }

            if (stats.TotalResultCount / stats.ExecutionCount > 500)
            {
                plan.Add("实现分页查询以减少数据传输量");
            }

            plan.Add("启用查询结果缓存");

            await Task.CompletedTask;
            return string.Join("; ", plan);
        }

        /// <summary>
        /// 生成查询分布统计
        /// </summary>
        private Dictionary<string, int> GenerateQueryDistribution(List<QueryStats> stats)
        {
            return stats
                .GroupBy(s => s.OperationName)
                .ToDictionary(g => g.Key, g => g.Sum(s => s.ExecutionCount));
        }

        /// <summary>
        /// 生成优化建议
        /// </summary>
        private List<string> GenerateOptimizationRecommendations(List<QueryStats> stats)
        {
            var recommendations = new List<string>();

            var slowQueries = stats.Count(s => s.AverageDuration > _slowQueryThreshold);
            if (slowQueries > 0)
            {
                recommendations.Add($"发现 {slowQueries} 个慢查询，建议优化数据库索引");
            }

            var highFrequencyQueries = stats.Where(s => s.ExecutionCount > 100).ToList();
            if (highFrequencyQueries.Any())
            {
                recommendations.Add($"发现 {highFrequencyQueries.Count} 个高频查询，建议启用缓存");
            }

            return recommendations;
        }

        /// <summary>
        /// 预热用户查询
        /// </summary>
        private async Task WarmupUserQueriesAsync(string tenantId)
        {
            try
            {
                // 预热常用用户查询
                await ExecuteOptimizedQueryAsync(
                    _dbContext.Users.Where(u => u.TenantId == tenantId).Take(50),
                    "GetActiveUsers", tenantId);
                
                _logger.LogDebug("用户查询预热完成: {TenantId}", tenantId);
            }
            catch (Exception ex)
            {
                _logger.LogWarning(ex, "用户查询预热失败: {TenantId}", tenantId);
            }
        }

        /// <summary>
        /// 预热内容查询
        /// </summary>
        private async Task WarmupContentQueriesAsync(string tenantId)
        {
            try
            {
                // 预热常用内容查询
                await ExecuteOptimizedQueryAsync(
                    _dbContext.Articles.Where(a => a.TenantId == tenantId).OrderByDescending(a => a.CreatedAt).Take(20),
                    "GetRecentArticles", tenantId);
                
                _logger.LogDebug("内容查询预热完成: {TenantId}", tenantId);
            }
            catch (Exception ex)
            {
                _logger.LogWarning(ex, "内容查询预热失败: {TenantId}", tenantId);
            }
        }

        /// <summary>
        /// 预热权限查询
        /// </summary>
        private async Task WarmupPermissionQueriesAsync(string tenantId)
        {
            try
            {
                // 预热权限查询
                await ExecuteOptimizedQueryAsync(
                    _dbContext.Roles.Where(r => r.TenantId == tenantId),
                    "GetTenantRoles", tenantId);
                
                _logger.LogDebug("权限查询预热完成: {TenantId}", tenantId);
            }
            catch (Exception ex)
            {
                _logger.LogWarning(ex, "权限查询预热失败: {TenantId}", tenantId);
            }
        }

        /// <summary>
        /// 预热配置查询
        /// </summary>
        private async Task WarmupConfigQueriesAsync(string tenantId)
        {
            try
            {
                // 预热配置查询
                await ExecuteOptimizedQueryAsync(
                    _dbContext.Templates.Where(t => t.TenantId == tenantId).Take(10),
                    "GetTenantTemplates", tenantId);
                
                _logger.LogDebug("配置查询预热完成: {TenantId}", tenantId);
            }
            catch (Exception ex)
            {
                _logger.LogWarning(ex, "配置查询预热失败: {TenantId}", tenantId);
            }
        }

        #endregion
    }

    #region 数据模型

    /// <summary>
    /// 查询结果
    /// </summary>
    public class QueryResult<T>
    {
        public List<T> Data { get; set; } = new();
        public bool FromCache { get; set; }
        public TimeSpan Duration { get; set; }
        public string QuerySignature { get; set; } = string.Empty;
    }

    /// <summary>
    /// 查询统计信息
    /// </summary>
    public class QueryStats
    {
        public string QuerySignature { get; set; } = string.Empty;
        public string OperationName { get; set; } = string.Empty;
        public string TenantId { get; set; } = string.Empty;
        public int ExecutionCount { get; set; }
        public TimeSpan AverageDuration { get; set; }
        public TimeSpan MaxDuration { get; set; }
        public TimeSpan MinDuration { get; set; }
        public DateTime LastExecution { get; set; }
        public int TotalResultCount { get; set; }
        public double CacheHitRate { get; set; }
    }

    /// <summary>
    /// 慢查询信息
    /// </summary>
    public class SlowQuery
    {
        public string QuerySignature { get; set; } = string.Empty;
        public string OperationName { get; set; } = string.Empty;
        public string TenantId { get; set; } = string.Empty;
        public int ExecutionCount { get; set; }
        public TimeSpan AverageDuration { get; set; }
        public TimeSpan MaxDuration { get; set; }
        public DateTime LastExecution { get; set; }
        public List<string> Recommendations { get; set; } = new();
    }

    /// <summary>
    /// 查询性能报告
    /// </summary>
    public class QueryPerformanceReport
    {
        public string? TenantId { get; set; }
        public int TotalQueries { get; set; }
        public int UniqueQueries { get; set; }
        public TimeSpan AverageQueryTime { get; set; }
        public int SlowQueriesCount { get; set; }
        public int CachedQueriesCount { get; set; }
        public List<QueryStats> TopSlowQueries { get; set; } = new();
        public Dictionary<string, int> QueryDistribution { get; set; } = new();
        public List<string> RecommendedOptimizations { get; set; } = new();
    }

    /// <summary>
    /// 查询优化计划
    /// </summary>
    public class QueryPlan
    {
        public string QuerySignature { get; set; } = string.Empty;
        public string OptimizationPlan { get; set; } = string.Empty;
        public DateTime CreatedAt { get; set; }
    }

    #endregion
}
